select ubi.CON_ANNO, 
       ubi.CON_ID,
       att.ATT_IDext,
       mis_valini, 
       gmis.SMIS_MAT, 
       gmis.CSTSM_DES,
       gmis.TSM_DES,
       gmis.COMGM_ID,
       gmis.GMSM_NUM,
       trunc(GMIS.VER_DTINI),
       trunc(GMIS.VER_DTFINE),
       trunc(ATTMIS.VER_DTINI),
       trunc(ATTMIS.VER_DTFINE),
       trunc(COM.VER_DTINI),
       trunc(COM.VER_DTFINE),
       gru.GMIS_ID,
       mis.FUCO_DES,
       mis.FACO_ID,
(select max(nvl(valgru.vcgm_val,' ')) from v_valcxgrumis valgru where valgru.az_id =gru.az_id and valgru.GMIS_ID =gru.GMIS_ID and valgru.STA_IDSYS =2 and  valgru.cgm_id =1 and valgru.VER_DTINI = gru.VER_DTINI) correttore,
(select max(nvl(valgru.vcgm_val,' ')) from v_valcxgrumis valgru where valgru.az_id =gru.az_id and valgru.GMIS_ID =gru.GMIS_ID and valgru.STA_IDSYS =2 and  valgru.cgm_id =2 and valgru.VER_DTINI = gru.VER_DTINI) coefficiente_k,
(select max(vcuf.CCON_VALVIS)
            from  v_valcxcuf  vcuf
            where ubi.CUF_ID = vcuf.CUF_ID
              and vcuf.CCON_ID  = 11
              and vcuf.STA_IDSYS =2
              and vcuf.az_id = '01'
              AND vcuf.ver_dtfine = (select max (a.VER_DTFINE)  from v_valcxcuf a where a.az_id = '01' and a.CUF_ID =  ubi.cuf_id  and a.CCON_ID  = 5)
              )      profilo_consumo      
from
v_attacco att,
grumis gru,
v_comgmis com,
v_gmissmis gmis,
v_misura mis,
v_conubifrn ubi,
v_attgmis attmis
where 
att.az_id ='01'
and gru.az_id ='01'
and com.az_id ='01'
and gmis.az_id ='01'
and mis.az_id ='01'
and ubi.az_id ='01'
and attmis.az_id ='01'
and ubi.SER_ID= '03'
and att.SER_ID='03'



and tatt_id='3'
  and att.ATT_ID = gru.ATT_ID
--and att.VER_DTFINE = to_date('31122009','ddmmyyyy')
  and ubi.ATT_ID = gru.ATT_ID
  and gru.GMIS_ID = com.GMIS_ID
  and com.COMGM_ID = gmis.COMGM_ID
  and gmis.GMSM_NUM = mis.GMSM_NUM
  and attmis.ATT_ID= ubi.ATT_ID
 -- and ubi.ATT_ID = 3039
  and ATTMIS.GMIS_ID = gru.GMIS_ID
  and attmis.STA_IDSYS =2
  and att.STA_IDSYS =2
--  and gru.STA_IDSYS =2
  and com.STA_IDSYS =2
  and gmis.STA_IDSYS =2
 -- and gMIS.VER_DTINI = TO_DATE('01012009','DDMMYYYY')
 -- and GMIS.VER_DTFINE = TO_DATE('31129999','DDMMYYYY') -- Per esaminare l'ultima lettura
  and GMIS.VER_DTINI = (select min (gmis1.ver_dtini)
                         from v_grumis gru1,
                              v_comgmis com1,
                              v_gmissmis gmis1
                        where ubi.ATT_ID = gru1.ATT_ID
                        and gru1.AZ_ID='01'
                        and com1.AZ_ID='01'
                        and gmis1.AZ_ID=ubi.az_id
                       
                          and gru1.GMIS_ID = com1.GMIS_ID
                          and com1.COMGM_ID = gmis1.COMGM_ID
                          and gru1.STA_IDSYS =2
                          and com1.STA_IDSYS =2
                          and gmis1.STA_IDSYS =2)
  
--and mis.STA_IDSYS =2
  and ubi.STA_IDSYS =2